package pers.bc.utils.constant;

/**
 * *
 * Created with Description:
 * 数据库sql语句关键词大全（简单），基本你能知道这几个关键词
 *
 * @version 1.0 <br>
 * @packagename: com.xisoft.common.constant<br>
 * @author: LiBencheng <br>
 * @computer: licheng <br>
 * @date: Created on 2018/8/18 17:11 <br>
 */
public interface ISqlCons
{

    String select_ts = " SELECT  %s,ts FROM %s WHERE %s/*主键key*/='%s' /*主键值*/ "; //AND ts='%s'/*时间戳*/
    // 简单的结构（增删改查）
    /** 增 */
    String insertSQL = " INSERT INTO %s(%s) VALUES (%s) ";
    /** 增 */
    String deleteSQL = " DELETE FROM %s WHERE %s ";
    /** 改 */
    String updateSQL = " UPDATE %s SET %s WHERE %s ";
    /** 查 */
    String selectSQL = " SELECT %s FROM %s ";
    /** case_when 结构 */
    String case_when = "case ... when ... then ... when ... then ... else ... end";

    // 简单的结构关键词
    String insert = " INSERT INTO ";
    String delete = " DELETE ";
    String update = " UPDATE ";
    String select = " SELECT ";
    String values = " VALUES ";
    String where = " WHERE ";

    String bracket = " ( %s ) ";
    // select下的关键词
    /** 全部 */
    String All = " * ";
    /** 设置字段别名 */
    String as = " AS ";
    /** 字符串转小写 */
    String lower = " LOWER ";
    /** 字符串转大写 */
    String upper = " UPPER ";
    /** 字符串截取 */
    String substr = " SUBSTR ";

    //数值型的单行函数
    /** 平均 */
    String avg = " AVG ";
    /** 保留x位小数 */
    String round = " ROUND( %s ) ";
    String trunc = " TRUNC( %s ) ";
    String mod = " MOD( %s ) ";
    /** 最大 */
    String max = " MAX( %s ) ";
    /** 总和 */
    String sum = " SUM( %s ) ";
    /** 计算个数 */
    String count = " COUNT( %s )";

    //日期型的单行函数
    String sysdate = " SYSDATE ";

    //转换函数
    String to_date = " TO_DATE( %s ) ";
    String to_char = " TO_CHAR( %s ) ";


    /** 去重 */
    String distinct = " DISTINCT ";
    /** 连接 示例：concat_ws( '11','22','33') 结果：112233 */
    String concat = " CONCAT( %s  ) ";
    /** 示例：concat_ws(',','11','22','33')/concat_ws(',','44','55',NULL);结果：11，22，33 / 44,55 */
    String concat_ws = " CONCAT_WS( %s ) ";
    /**
     * group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
     * 示例： group_concat(name  separator ';')  結果: 10;20
     */
    String group_concat = " GROUP_CONCAT( %s ) ";

    // from下的关键词
    /** 左连接 */
    String left_join = " LEFT JOIN ";
    /** 右连接 */
    String right_join = " RIGHT JOIN ";
    /** 外连接 */
    String outer_join = " OUTER JOIN ";
    /** 满连接 */
    String full_join = " FULL JOIN ";
    /** 连接条件 */
    String on = " ON ";

    // 集合操作函数
    /** (无重并集)自动去掉结果集中的重复行，并以第一列的结果进行升序排序。 */
    String union = " UNION ";
    /** (有重并集)不去掉重复行，并且不对结果集进行排序 */
    String union_all = " UNION ALL ";
    /** (交集)：取两个结果集的交集，并且以第一列的结果进行升序排列。 */
    String intersect = " INTERSECT ";
    /** (差集)只显示在第一个集合中存在，在第二个集合中不存在的数据。并且以第一列的结果进行升序排序。 */
    String minus = " MINUS ";

    // where下的关键词
    /** 左包含left */
    String left_like = " LIKE '%%s'";
    /** 右包含left */
    String right_like = " LIKE '%s%'";
    /** 包含 Middle */
    String center_like = " LIKE '%%s%'";
    /** 20-23岁（包括20和23）之间 */
    String between_and = " BETWEEN %s AND %s";
    /** 不在~之间 */
    String not_between_and = " NOT BETWEEN  %s AND %s ";
    /** 存在括号内的即为真 */
    String in = " IN (%s) ";
    /** 不存在 */
    String not_in = " NOT IN (%s)  ";
    /** 不存在 */
    String not_exists = " NOT EXISTS ";
    /** 为空，grade为字段 */
    String grade_is_null = " GRADE IS NULL ";
    /** 不为空 */
    String is_not_null = " IS NOT  NULL  ";
    /** 且 */
    String and = " AND ";
    /** 或 */
    String or = " OR ";

    /** 小于 */
    String left_chev = " < ";
    /** 等于 */
    String equals = " = ";
    /** 大于 */
    String right_chev = " > ";
    /** 小于全部 */
    String right_chev_all = " <all ";
    /** 不等于 */
    String unequals = " <> ";
    /** 小于任意一个 */
    String right_chev_any = " <any  ";


    // 分组排序下的关键词
    String group_by = " GROUP BY ";
    String order_by = " ORDER BY ";
    String desc = " DESC ";
    String asc = " ASC ";
    String by = " BY ";

    //
    String alter = " ALTER ";
    String identified = " IDENTIFIED ";
    String view = " VIEW ";
    String table = " TABLE ";
    String set = " SET ";
    String unused = " UNUSED ";
    String drop = " DROP ";
    String rename = " RENAME ";
    String ROLLBACK = " ROLLBACK ";
    String modify = " MODIFY ";

    ///////////////////////////////////////////////
    /** 向表中添加 新列 */
    String addDefTableSql = "  ALTER TABLE  %s/*表名*/ ADD(%s/*列名*/ %s/*类型名(长度)*/ DEFAULT %s/*默认值*/) ; ";

    /** 向表中添加 新列 */
    String addTableSql = "  ALTER TABLE %s/*表名*/  ADD(%s/*列名*/ %s/*类型名(长度)*/) ) ; ";

    /** 修改表 中字段长度： */
    String modifyTableSql = " ALTER TABLE %s/*表名*/ MODIFY (%s/*列名*/ %s/*类型名(长度)*/) ;";

    /** 将表中列设置成不可用 */
    String setTableSql = " ALTER TABLE %s/*表名*/  SET UNUSED COLUMN %s/*列名*/  ;";

    /** 删除表中不可用的列 */
    String dropTableUnSql = " ALTER TABLE %s/*表名*/  DROP UNUSED COLUMN %s/*列名*/; ";

    /** 删除表中的列 */
    String dropTableSql = " ALTER TABLE %s/*表名*/ DROP COLUMN %s /*列名*/;";

    /** 注释sql */
    String commentSql = "COMMENT ON COLUMN %s/*表名.列名*/ IS '%s'/*注释*/;";

    /** 重命名表 */
    String renameSql = " RENAME  %s/*表名*/ TO  %s/*新表名*/; ";


}
